#!python
# -*- coding: utf-8 -*-
import pymysql
import traceback
from cgi import parse_qs
from cgi import FieldStorage
import json
import base64
from string import Template
import time
import os
from Crypto.Cipher import ARC4
from Crypto import Random
import socket
import StringIO
import urlparse
import urllib

random_fp = Random.new()

def rc4(key, data):
    r = ARC4.new(key)
    d = r.encrypt(data)
    return d

class Dbconn:
    def __init__(self, host, user, passwd, db):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.conn=None

    def connect(self):
        if not self.conn:
            self.conn = pymysql.connect(self.host,
                    self.user, self.passwd, self.db, charset="utf8", use_unicode=True)
        #self.conn.set_character_set("latin1")
        try:
            cur=self.conn.cursor()
            cur.execute("select 1+1 from dual where 0 > 1")
            cur.fetchall()
            cur.close()
            try:
                self.conn.commit()
            except:
                pass
        except:
            traceback.print_exc()
            try:
                self.conn.close()
            except:
                pass
            self.conn = pymysql.connect(self.host,
                    self.user, self.passwd, self.db, charset="utf8", use_unicode=True)
        return self.conn

db = Dbconn("www.simicloud.com","video","MuM5Z9rbdc4MrUeT","video")
db1 = Dbconn("www.simicloud.com","opensips","opensipsrw.","opensips")

def application(env, start_response):
    path=env["PATH_INFO"]
    for p,f in url_maps:
        if p == path:
            return f(env, start_response)
    start_response("200 OK",[("Content-Type","text/plain")])
    data=[]
    for k,v in env.items():
        data.append("%s=%s\n" % (k,v))
    return data

def get_img(env, start_response):
    try:
        conn=db.connect()
    except:
        traceback.print_exc()
        start_response("500 Internal error",[])
        return ['']

    #start = 0
    #count = 10
    q=env["QUERY_STRING"]
    q1=parse_qs(q)
    img_id = 0
    if q1.has_key("id"):
       img_id = int(q1["id"][0])
    if img_id == 0:
        start_response("400 Bad Request",[("Content-Type","text/plain")])
        return ["id must specialled"]

    cur = conn.cursor()
    cur.execute("select resource_image from video where id = %d" % (img_id,))
    imgs=cur.fetchall()
    cur.close()
    if not imgs:
        start_response("404 not found",[("Content-Type","text/plain")])
        return [""]
    if not imgs[0][0]:
        start_response("404 not found",[("Content-Type","text/plain")])
        return [""]
    #print imgs
    data = base64.b64decode(imgs[0][0])
    start_response("200 OK",[("Content-Type","image/jpeg")])
    return [data]

def get_list(env, start_response):
    try:
        conn=db.connect()
    except:
        traceback.print_exc()
        start_response("500 Internal error",[])
        return ['']

    q1 = FieldStorage(fp=env["wsgi.input"],
            environ = env
            )
    try:
        # start
        start = int(q1.getvalue("start", 0))

        # count
        count = int(q1.getvalue("count", 10))
    except:
        start_response("400 Bad Request",[])
        return ["400 Bad Request"]

    # output
    output = q1.getvalue("output", "json")

    # download_url
    download_url = q1.getvalue("download_url", "")

    # movie_name
    movie_name = q1.getvalue("movie_name", "")

    # catalog
    catalog=q1.getvalue("catalog", "")

    # encrypted name
    enc_name = q1.getvalue("enc_name", "")

    # sql
    sql = """select id, origin_name, description, download_url, img_url,
        catalog, artist, year, movie_name, location, director, upload_time
        from video """

    if movie_name:
        sql += "where movie_name = '%s' and download_url is not null limit %d,%d" % (pymysql.escape_string(movie_name), start, count)
    elif enc_name:
        sql += "where current_name = '%s'" % (enc_name,)
    elif download_url:
        sql +=  "where download_url = '%s'" % pymysql.escape_string(download_url)
    elif catalog:
        sql += "where catalog = '%s' and download_url is not null order by upload_time desc limit %d,%d" % (pymysql.escape_string(catalog), start, count)
    else:
        sql += "where download_url is not null order by upload_time desc limit %d, %d" % (start, count)

    cur = conn.cursor()
    cur.execute(sql)
    result = cur.fetchall()
    cur.close()

    data = []
    if output == "html":
        data.append(u"""<html>
        <head><title>电影列表</title></head><style type="text/css" >
        table, td, tr, th {border: 1px solid #ccc;border-collapse: collapse; font-size: 15px;}
        /*table { width: 90%; }*/
        .desc{ word-break: break-all; word-wrap: break-word; }
        .center { text-align: center;}
        td { padding: 10px; }
        </style>
        <body><h1>电影列表</h1>""")

        data.append(u"""<table><tr>
                <th style="width: 3%">编号</th>
                <th style="width: 14%">剧集名称</th>
                <th style="width: 20%">电影简介</th>
                <th style="width: 10%">电影名称</th>
                <th style="width: 24%">图片地址</th>
                <th style="width: 3%">分类</th>
                <th style="width: 10%">演员列表</th>
                <th style="width: 3%">上映时间</th>
                <th>地区</th>
                <th>导演</th>
                <th>操作</th>""")

    num_rows = 0
    for row in result:
        num_rows += 1
        if output == "json":
            data.append(
                    dict(
                        name = row[1],
                        img_url=row[4],
                        description = row[2],
                        download_link = row[3],
                        movie_name  = row[8],
                        artist = row[6],
                        catalog = row[5],
                        year = int(row[7]) if row[7] else 0,
                        location = row[9],
                        director = row[10],
                        update_time = row[11].strftime("%Y-%m-%d")
                    )
                )
        elif output == "html":
            d = u"""<tr>
            <td class="center">{num}</td>
            <td><div class="desc">{name}</div></td>
            <td><div class="desc">{desc}</div></td>
            <td><a href="list?output=html&movie_name={movie_name}">{movie_name}</a></td>
            <td><div class="desc"><a href="{img_url}">{img_url}</a></div></td>
            <td class="center"><a href="list?output=html&catalog={catalog}">{catalog}</a></td>
            <td>{artist}</td>
            <td class="center">{year}</td>
            <td>{location}</td>
            <td>{director}</td>
            <td class="center"><a href="edit?id={id}">修改</a></td>
            </tr>""" .format(**dict(
                    num = num_rows + start,
                    name = row[1] if row[1] else "",
                    desc = row[2].replace("\n","<br />") if row[2] else "",
                    movie_name = row[8] if row[8] else "",
                    img_url=row[4] if row[4] else "",
                    catalog = row[5] if  row[5] else "",
                    artist = row[6] if row[6] else "",
                    year = row[7] if row[7] else "0",
                    id = row[0],
                    location = row[9] if row[9] else "",
                    director = row[10] if row[10] else "",
                )
            )

            data.append(d)

    if output == "json":
        start_response("200 OK",[("Content-Type","application/json")])
        return [json.dumps(data)]
    elif output == "html":
        mov =  ""
        if movie_name:
            mov = "&movie_name=%s" % movie_name.decode("utf-8")
        data.append("""
                </table>
                <p><a href="list?output=html&start=%d&count=%d%s">Next</a></p>
                </body>
                </html>""" % (start+count, count, mov))

        start_response("200 OK",[("Content-Type","text/html; charset=utf-8")])
        return ["".join(data).encode("utf-8")]


def edit_item(env, start_response):
    conn= db.connect()
    cur = conn.cursor()

    q1 = FieldStorage(fp=env["wsgi.input"], environ = env)
    try:
        id = int(q1.getvalue("id",0))
    except:
        start_response("400 Bad Request",[])
        cur.close()
        return ["400 Bad Request"]

    if env["REQUEST_METHOD"] == "GET":
        referer = env.get("HTTP_REFERER")
        if id == 0:
            start_response("404 Not found", [])
            cur.close()
            return ["Not found"]
        cur.execute("""select origin_name, img_url, description, movie_name, catalog, artist, year, location, director
                from video where id = %d order by upload_time desc""" % id)
        res=  cur.fetchone()
        if not res:
            cur.close()
            start_response("200 OK", [("Content-Type", "text/plain")])
            return ["this id is not exists"]

        t = dict(
            origin_name = res[0] if res[0] else "unknow movie",
            id = id,

            img_url = res[1] if res[1] else "",
            desc = res[2] if res[2] else "",
            movie_name = res[3] if res[3] else "",
            catalog = res[4] if res[4] else "",
            artist = res[5] if res[5] else "",
            year = res[6] if res[6] else "",
            referer = referer if referer else "",
            location = res[7] if res[7] else "",
            director = res[8] if res[8] else ""
        )

        tpl = os.path.join(os.path.dirname(__file__), "a.html")
        d = Template(open(tpl).read().decode("utf-8")).safe_substitute(t)

        cur.close()
        start_response("200 OK",[("Content-Type","text/html; charset=utf-8")])
        return [d.encode("utf-8")]

    elif env["REQUEST_METHOD"] == "POST":
        origin_name = q1.getvalue("origin_name", "")
        img_url = q1.getvalue("img_url", "")
        desc = q1.getvalue("desc", "")
        movie_name = q1.getvalue("movie_name", "")
        catalog=q1.getvalue("catalog", "")
        artist=q1.getvalue("artist", "")
        try:
            year = int(q1.getvalue("year", 0))
        except:
            year = 0
        return_url = q1.getvalue("return_url", "")
        location = q1.getvalue("location", "")
        director = q1.getvalue("director", "")
        cur.execute("""update video set origin_name='{origin_name}', img_url='{img_url}',
                description='{desc}', movie_name='{movie_name}', catalog='{catalog}',
                artist='{artist}', year={year}, location='{location}', director='{director}'
                where id={id}
                """ .format (**dict(
                    origin_name = pymysql.escape_string(origin_name),
                    img_url = pymysql.escape_string(img_url),
                    desc = pymysql.escape_string(desc),
                    movie_name = pymysql.escape_string(movie_name),
                    catalog = pymysql.escape_string(catalog),
                    artist = pymysql.escape_string(artist),
                    year = year,
                    id = id,
                    location = pymysql.escape_string(location),
                    director = pymysql.escape_string(director),
                )
           )
        )
        conn.commit()
        cur.close()
        if return_url:
            start_response("302 Found",[("Location",return_url)])
            return []
        start_response("200 OK", [("Content-Type","text/html; charset=utf-8")])
        return ['<p>change ok</p><p><a href="edit?id=%d">Next</a>' % (int(id) + 1,)]

def info(env, start_response):
    method = env.get("REQUEST_METHOD")
    if method != "POST":
        start_response("200 OK", [])
        return ["ok"]

    rc4_key = "pnNYugersZ1jyhgPohyBFdg9"
    fp_in = env["wsgi.input"]

    content_length=int(env.get("CONTENT_LENGTH", 0))

    if not content_length:
        start_response("400 Need CONTENT_LENGTH", [])
        return ["400 Need conetn_length"]
    content_type = env.get("CONTENT_TYPE", "")

    if "json" not in content_type:
        start_response("400 Wrong CONENT_TYPE", [])
        return ["400 Wrong content_type"]

    #enc = env.get("HTTP_CONTENT_ENCODING")
    data = fp_in.read(content_length)

    #d1 = rc4(rc4_key, data)
    d1 = data
    try:
        d2 = json.loads(d1)
        #print "got data '%s'" % d2
    except:
        start_response("400 Bad Format", [])
        return ["400 Bad Format"]

    userinfo=d2["userInfo"]
    statis = d2["statisticsInfo"]

    if not userinfo.has_key("terminalId"):
        start_response("400 Bad format",[])
        return ["400 missing some key"]

    tmp_ui = {}
    tmp_st = {}

    for k,v in userinfo.items():
        if isinstance(v, str):
            v = pymysql.escape_string(v)
        elif isinstance(v, unicode):
            v = pymysql.escape_string(v.encode("utf-8"))
        elif isinstance(v, list):
            pass
        elif isinstance(v, dict):
            pass

        if isinstance(k, unicode):
            k = k.encode("utf-8")
        tmp_ui[k] = v

    for k,v in statis.items():
        if isinstance(v, str):
            v = pymysql.escape_string(v)
        elif isinstance(v, unicode):
            v = pymysql.escape_string(v.encode("utf-8"))
        elif isinstance(v, list):
            pass
        elif isinstance(v, dict):
            pass

        if isinstance(k, unicode):
            k = k.encode("utf-8")

        tmp_st[k] = v

    userinfo = tmp_ui
    statis = tmp_st

    if not statis.has_key("baiduLiveness"):
        statis["baiduLiveness"] = 0

    if not statis.has_key("musicDuration"):
        statis["musicDuration"] = 0

    if not userinfo.has_key("deviceId"):
        userinfo["deviceId"] = ""

    if not userinfo.has_key("baiduAccount"):
        userinfo["baiduAccount"] = ""

    is_test = int(d2.get("test", 0))

    if is_test:
        user_tbl = "t_userinfo"
        mtime_tbl = "t_mediatimes"
        mname_tbl = "t_movienames"
    else:
        user_tbl = "userinfo"
        mtime_tbl = "mediatimes"
        mname_tbl = "movienames"

    conn = db.connect()
    cur = conn.cursor()
    sql = """select id from {user_tbl}
    where
        terminalid='{terminalId}'
        and deviceid='{deviceId}'
    """
    s = sql.format(user_tbl=user_tbl, **userinfo)
    #print s
    cur.execute(s)
    rs = cur.fetchall()
    n_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))

    userinfo["last_update"] = n_time
    statis["last_update"] = n_time

    # no record
    if len(rs) == 0:
        sql = """insert into {user_tbl} (terminalid, deviceid, terminaltype,terminalversion,clientversion,
                firmwareversion,baiduaccount,last_update,createdate
                )
               values(
                    '{terminalId}','{deviceId}','{terminalType}','{terminalVersion}','{clientVersion}',
                    '{firmwareVersion}','{baiduAccount}','{last_update}','{createdate}'
                    )
                    """
        s = sql.format(user_tbl = user_tbl, createdate=n_time, **userinfo)
        #print s
        cur.execute(s)
        conn.commit()
    else:
        # has record, check if version is changed
        sql = """select id from {user_tbl}
        where
            terminalid='{terminalId}'
            and deviceid = '{deviceId}'
            and terminalversion='{terminalVersion}'
            and clientversion = '{clientVersion}'
            and firmwareversion='{firmwareVersion}'
            and baiduaccount = '{baiduAccount}'
        """
        s = sql.format(user_tbl=user_tbl, **userinfo)
        cur.execute(s)
        rs = cur.fetchall()

        # version is changed
        if len(rs) == 0:
            sql = """update {user_tbl} set
                terminalversion='{terminalVersion}',
                clientversion='{clientVersion}',
                baiduaccount='{baiduAccount}',
                firmwareversion='{firmwareVersion}',
                last_update='{last_update}'
            where
                terminalid='{terminalId}'
                and deviceid='{deviceId}'
                """
            s = sql.format(user_tbl=user_tbl, **userinfo)
            cur.execute(s)
            conn.commit()


    m=[]
    m_cols=[]
    m_values=[]

    if int(statis["pictureDuration"]):
        m.append("picturetime=picturetime+%d" % (int(statis["pictureDuration"]),))
        #m.append("pictureliveness=pictureliveness+1")
        m_cols.append("picturetime")
        m_values.append("'{pictureDuration}'")

    if int(statis["videoDuration"]):
        m.append("videotime=videotime+%d" % (int(statis["videoDuration"]),))
        #m.append("videoliveness=videoliveness+1")
        m_cols.append("videotime")
        m_values.append("'{videoDuration}'")

    if int(statis["documentDuration"]):
        m.append("documenttime=documenttime+%d" % (int(statis["documentDuration"]),))
        #m.append("documentliveness=documentliveness+1")
        m_cols.append("documenttime")
        m_values.append("'{documentDuration}'")

    if int(statis["startCount"]):
        m.append("startcount=startcount+%d" % (int(statis["startCount"]),))
        #m.append("openliveness=openliveness+1")
        m_cols.append("startcount")
        m_values.append("'{startCount}'")

    if int(statis["musicDuration"]):
        m.append("musictime=musictime+%d" % (int(statis["musicDuration"]),))
        #m.append("musicliveness=musicliveness+1")
        m_cols.append("musictime")
        m_values.append("'{musicDuration}'")

    if int(statis["baiduLiveness"]):
        m.append("baiduliveness=baiduliveness+1")

    if m or m_cols:
        r_date = statis["createTime"]
        sql = "select id from {mtime_tbl} where terminalid='{terminalId}' and recorddate='{r_date}'"
        s = sql.format(mtime_tbl=mtime_tbl, terminalId = userinfo["terminalId"], r_date = r_date)
        cur.execute(s)
        rs = cur.fetchall()
        if len(rs) > 0:
            m.append("last_update='{last_update}'")
            sql = "update {mtime_tbl} set %s where terminalid='{terminalId}' and recorddate='{r_date}'" % (",".join(m),)
            s = sql.format(mtime_tbl = mtime_tbl, r_date = r_date, **userinfo)
            cur.execute(s)
            conn.commit()
        else:
            m_cols.append("terminalid")
            m_values.append("'{terminalId}'")
            m_cols.append("last_update")
            m_values.append("'{last_update}'")
            m_cols.append("recorddate")
            m_values.append("'{r_date}'")
            sql = "insert into {mtime_tbl} (%s) values(%s)" % (",".join(m_cols), ",".join(m_values))
            statis["terminalId"] = userinfo["terminalId"]
            s = sql.format(mtime_tbl=mtime_tbl, r_date = r_date, **statis)
            cur.execute(s)
            conn.commit()

    movie_names = statis["videoNames"]
    names = []

    for v in movie_names:
        parsed = urlparse.urlparse(v)
        u1 = urllib.unquote(parsed.path.encode("utf-8"))
        bname = os.path.basename(u1)
        names.append((userinfo["terminalId"], pymysql.escape_string(bname), n_time))

    if names:
        sql = "insert into {mname_tbl} (terminalid, videoname, watch_time) values(%s, %s, %s)"
        cur.executemany(sql.format(mname_tbl = mname_tbl), names)
        conn.commit()

    cur.close()

    start_response("200 OK", [("Content-Type", "application/json")])

    return [
            json.dumps({"time": statis["createTime"]})
            ]

def send_msg(msg, users):
    key = "bd1146b4cfa503eb2c1056b693377f30"
    domain = "sip2.simicloud.com"
    port = 50600
    user = "POWER7_webmaster"
    METHOD='MESSAGE'
    sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    sock.settimeout(5)
    text_tpl="""{method} sip:{to_user}@{domain} SIP/2.0\r\nVia: SIP/2.0/UDP 127.0.0.1;rport;branch=z9hG4bk{branch}\r\nFrom: <sip:{from_user}@{domain}>;tag={tag}\r\nTo: <sip:{to_user}@{domain}>\r\nCall-ID: {callid}\r\nMax-Forwards: 10\r\nContact: <sip:{from_user}@127.0.0.1>\r\nExpires: 20\r\nCSeq: {cseq} {method}\r\nAllow: OPTIONS,INFO,MESSAGE\r\nContent-Type: text/plain\r\nContent-Length: {clen}\r\nUser-Agent: pythonlib/2.7\r\n\r\n{msg}"""

    d = dict(
        method=METHOD,
        to_user="",
        from_user=user,
        domain=domain,
        tag=random_fp.read(10).encode("hex"),
        branch=random_fp.read(10).encode("hex"),
        callid=random_fp.read(5).encode("hex"),
        cseq=int(random_fp.read(1).encode("hex"),16),
        clen=len(msg),
        msg=msg
    )
    for u in users:
        d["to_user"] = u
        text=text_tpl.format(**d)
        #print "send msg to %s" % u
        sock.sendto(rc4(key,text),(domain,port))

    rs_msgs = []
    for i in range(len(users)):
        try:
            data,addr=sock.recvfrom(512)
        except:
            break
        rs_msgs.append(rc4(key,data))
    sock.close()
    oks = []
    fails = []
    for r in rs_msgs:
        fp = StringIO.StringIO(r)
        firstline = fp.readline()
        code = int(firstline.split(" ",2)[1])
        for u in users:
            if u in r:
                #print u,code
                if code == 200:
                    oks.append(u)
                else:
                    fails.append(u)
                break
    for u in users:
        if (u not in oks)  and (u not in fails):
            fails.append(u)
    return oks,fails

def push(env, start_response):
    method = env.get("REQUEST_METHOD")
    if method == "GET":
        conn = db1.connect()
        cur = conn.cursor()
        cur.execute("select username from location limit 200")
        res = cur.fetchall()
        cur.close()
        vendors = []
        users = []
        for r in res:
            u = str(r[0])
            v = u.split("_", 1)[0]
            if v not in vendors:
                vendors.append(v)
            users.append(u)
        tpl = os.path.join(os.path.dirname(__file__), "b.html")
        vendors_html = ""
        for v in vendors:
            v1 = """<option value="%s">%s</option>\n""" % (v,v)
            vendors_html ="%s%s" % (vendors_html, v1)
        users_html = ""
        for u in users:
            u1= """<option value="%s">%s</option>\n""" % (u,u)
            users_html = "%s%s" % (users_html, u1)
        d = Template(open(tpl).read().decode("utf-8")).safe_substitute(
                {"vendors_html":vendors_html,
                    "users_html":users_html
                    }
                )
        start_response("200 OK", [("Content-Type","text/html; charset=utf-8")])
        return [d.encode("utf-8")]
    elif method == "POST":
        forms = FieldStorage(
                fp=env["wsgi.input"],
                environ = env
                )
        msg = forms.getvalue("msg")
        if not msg:
            start_response("200 OK", [])
            return ["no message entered"]

        users=forms.getlist("users")
        if not users:
            start_response("200 OK", [])
            return ["no users selected"]

        oks,fails=send_msg(msg, users)
        start_response("200 OK", [("Content-Type", "text/html; charset=utf-8")])
        tpl = os.path.join(os.path.dirname(__file__), "c.html")
        d = Template(open(tpl).read().decode("utf-8")).safe_substitute(
                {
                    "msg":msg.replace("\n","<br>"),
                    "success":str(oks),
                    "failed":str(fails)
                    }
                )
        return [d.encode("utf-8")]
    else:
        start_response("400 Bad Request", [])
        return ["400 Bad Request"]

def user_info(env, start_response):
    method = env.get("REQUEST_METHOD")
    if method == "GET":
        fn = os.path.join(os.path.dirname(__file__), "add_info.html")
        start_response("200 OK", [("Content-Type","text/html; charset=utf-8")])
        data = open(fn).read()
        return [data]

    form = FieldStorage(environ = env, fp = env.get("wsgi.input"))

    user = form.getvalue("c_name")
    sn = form.getvalue("c_sn")
    phone = form.getvalue("c_phone")
    t = form.getvalue("c_time")
    start_response("200 OK",[("Content-Type","text/plain")])
    return ["%s %s %s %s" % (user,sn,phone,t)]

def report(env, start_response):
    conn = db.connect()
    cur = conn.cursor()

    start = time.strftime("%Y-%m-%d %H:00:00", time.localtime(time.time() - 24 * 60 * 60))
    end = time.strftime("%Y-%m-%d %H:%M:%S")

    sql = "select count(id) from userinfo where createdate > '%s' and  createdate < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    new_user = int(rs[0][0])

    sql = "select count(id) from mediatimes where last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    active_user = int(rs[0][0])

    sql = "select count(videotime) from mediatimes where videotime > 0 and last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    video_user = int(rs[0][0])

    sql = "select count(musictime) from mediatimes where musictime > 0 and last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    music_user = int(rs[0][0])

    sql = "select count(picturetime) from mediatimes where picturetime > 0 and last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    picture_user = int(rs[0][0])

    sql = "select count(documenttime) from mediatimes where documenttime > 0 and last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    document_user = int(rs[0][0])

    sql = "select count(baiduliveness) from mediatimes where baiduliveness > 0 and last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    baidu_user = int(rs[0][0])

    sql = "select sum(videotime),sum(musictime),sum(documenttime),sum(picturetime),sum(startcount) from mediatimes where last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs=cur.fetchall()
    videotime,musictime,doctime,picturetime,startcount = rs[0]

    sql = "select count(id) from userinfo"
    cur.execute(sql)
    rs=cur.fetchall()
    total_user = rs[0][0]

    tpl = os.path.join(os.path.dirname(__file__), "status_rpt.html")
    d = Template(open(tpl).read().decode("utf-8")).safe_substitute(**locals())

    cur.close()

    start_response("200 OK",[("Content-Type","text/html; charset=utf-8")])
    return [d.encode("utf-8")]

url_maps=[
        ("/", get_list),
        ("/list",get_list),
        ("/image",get_img),
        ("/edit", edit_item),
        ("/info", info),
        ("/push", push),
        ("/user", user_info),
        ("/report", report),
        ]


if __name__ == "__main__":
    from gevent import monkey
    monkey.patch_all()
    from gevent.pywsgi import WSGIServer
    WSGIServer(('', 8080), application).serve_forever()
